package fr.gadcparis.envieparisienne.model;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.StringReader;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.location.Address;
import android.location.Geocoder;
import android.location.Location;
import android.util.Log;
import fr.gadcparis.envieparisienne.fusiontables.ftclient.ClientLogin;
import fr.gadcparis.envieparisienne.fusiontables.ftclient.FtClient;

public class FtUsersInformations extends FtDataClient {
	
	private static final String TAG = "FtUsersInformations";
	private static final String TABLE_ID = "1tFXUduPKUi_Qsdwtq8zS3H-n5VcbaM7X9U8X5Ps";
	

	public FtUsersInformations(Context context) {
		super(context);
	}

	
	public void select (Location location) {
		Log.d(TAG,"select " + location);	
	    StringBuilder select = new StringBuilder();
	    select.append("SELECT * from ");
        select.append(TABLE_ID);	  
        select.append(" WHERE ");	   
	    select.append(" ST_INTERSECTS(location, CIRCLE(");
	    select.append("LATLNG(" + location.getLatitude() + "," + location.getLongitude() + "),"+ RAYON_METRE + "))");
/*	    
	    select.append("SELECT * FROM 2975542 WHERE"
	    		" ST_INTERSECTS('Adresse complete', CIRCLE(LATLNG(48.856614,2.352222),1000))");//* FROM*/
	    Log.d(TAG,"select " + select);	
	    // Save the data to Fusion Tables
        String result = ftclient.query(select.toString());	
        parseResults(result);
	}
	
	public void select (String datasetId, int id) {
	    StringBuilder select = new StringBuilder();
	    select.append("SELECT * from ");
        select.append(TABLE_ID);	
        select.append(" WHERE dataset_id='");
        select.append(datasetId);
        select.append("' AND rwo_id=");
        select.append(id); 
        select.append(" ORDER BY date_creation DESC LIMIT 1"); 
        
	    Log.d(TAG,"select " + select);	
	    
	    // Save the data to Fusion Tables
        String result = ftclient.query(select.toString());
	    Log.i(TAG,"result " + result);		
        parseResults(result);
	}
	
	public boolean insert(String item, Location location, String commentaire,
			int rwo_id, int existant, String dataset_id, int disfonctionne ) {
		// Get the dateTime
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date();
        
	    StringBuilder insert = new StringBuilder();
        insert.append("INSERT INTO ");
        insert.append(TABLE_ID);
        insert.append(" (item, adresse, location, date_creation, nb_alert, commentaire," +
        		" rwo_id, existant, dataset_id, disfonctionne)");
        insert.append(" VALUES ('");
        insert.append(item); // Type d'item
        insert.append("','");
        insert.append(this.getAdresse(location)); // Adresse
        insert.append("','");
        insert.append(location.getLatitude() + " " + location.getLongitude()); // Coords
        insert.append("','");
        insert.append(dateFormat.format(date)); // Date Time
        insert.append("',");
        insert.append(1); // Initialise
        insert.append(",'");
        insert.append(commentaire); // Commentaire
        insert.append("',");
        insert.append(rwo_id); 
        insert.append(",");
        insert.append(existant); // 
        insert.append(",'");
        insert.append(dataset_id); // 
        insert.append("',");
        insert.append(disfonctionne); // 
        insert.append(")");

	    Log.d(TAG,"insert " + insert);	
	    // Save the data to Fusion Tables
        String result = ftclient.query(insert.toString());
        return (result.indexOf("rowid") != -1) ; 
	}
	
	public boolean updateAlert(String datasetId, int id, boolean validation) {
	    StringBuilder update = new StringBuilder();
	    update.append("UPDATE ");
	    update.append(TABLE_ID);
	    if (validation) 
	    	update.append(" SET nb_alert+=1");
	    else
	    	update.append(" SET nb_alert-=1");

	    update.append(" WHERE dataset_id='");
	    update.append(datasetId);
	    update.append("' AND rwo_id=");
	    update.append(id); 

	    Log.d(TAG,"update " + update);	
	    // Save the data to Fusion Tables
        String result = ftclient.query(update.toString());
	    Log.d(TAG,"result " + result);	
        return (result.indexOf("rwo_id") != -1) ; 
	}	
	
	public boolean updateExists(String datasetId, int id, int exists) {
	    StringBuilder update = new StringBuilder();
	    update.append("UPDATE ");
	    update.append(TABLE_ID);
	    update.append(" SET existant=");
	    update.append(exists);

	    //update.append(" WHERE dataset_id='");
	    //update.append(datasetId);
	    update.append(" WHERE rwo_id=");
	    update.append(id); 

	    Log.d(TAG,"update " + update);	
	    // Save the data to Fusion Tables
        String result = ftclient.query(update.toString());
	    Log.d(TAG,"result " + result);	
        return (result.indexOf("rwo_id") != -1) ; 
		
	}
	
	public boolean updateProblem(String datasetId, int id, int disfonctionne) {
	    StringBuilder update = new StringBuilder();
	    update.append("UPDATE ");
	    update.append(TABLE_ID);
	    update.append(" SET disfonctionne=");
	    update.append(disfonctionne);

	    update.append(" WHERE dataset_id='");
	    update.append(datasetId);
	    update.append("' AND rwo_id=");
	    update.append(id); 

	    Log.d(TAG,"update " + update);	
	    // Save the data to Fusion Tables
        String result = ftclient.query(update.toString());
	    Log.d(TAG,"result " + result);	
        return (result.indexOf("rwo_id") != -1) ; 
		
	}
}
